package com.office.web;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.office.bean.Classes;
import com.office.bean.Student;
import com.office.bean.StudentInfo;
import com.office.service.ClassesService;
import com.office.service.StudentService;
import com.office.utils.PageUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
public class StudentController {

    @Autowired
    StudentService studentService;

    @Autowired
    ClassesService classesService;


    /***
     * 分页及搜索功能
     * @param pageIndex
     * @param pageSize
     * @param name
     * @param class_id
     * @param model
     * @return
     */
    @RequestMapping("/student_list")
    public String Student_list(int pageIndex, int pageSize, String name, @RequestParam(defaultValue = "0") Integer class_id, Model model) {
        Map<String ,Object> map = new HashMap<>();
        map.put("pageStart",(pageIndex-1)*pageSize);
        map.put("pageSize",pageSize);
        map.put("name",name);
        map.put("class_id",class_id);
        long totalCount = studentService.findTotalCount(map);
        List<Student> studentsByPage = studentService.findStudentsByPage(map);
        PageUtils<Student> PageUtils = new PageUtils<>(pageIndex,pageSize,totalCount,studentsByPage);
        model.addAttribute("PageUtils",PageUtils);
        model.addAttribute("name",name);
        model.addAttribute("class_id",class_id);
        return "studentlist";
    }


    /**
     * 查询所有的的班级
     * @return
     */
    @RequestMapping("/student_findClasses")
    @ResponseBody
    public List<Classes> findClasses(){
        return classesService.findClassList();
    }


    /**
     * 删除
     * @param id
     * @return
     */

    @RequestMapping("/deleteStudent")
    @ResponseBody
    public boolean deleteStudent(int id){
        return studentService.deleteStudent(id);
    }

    /**
     * 跳转更新页面
     */
    @RequestMapping("/student_toUpdate/{id}")
    public String student_toUpdate(@PathVariable("id")int id,Model model){
        Student student = studentService.findStudentById(id);
        model.addAttribute("student",student);
        return "studentupdate";
    }

    /**
     * 更新学生信息
     * @param student
     * @param response
     * @throws Exception
     */
    @RequestMapping("/student_update")
    public void student_update(Student student, HttpServletResponse response) throws Exception{
        response.setContentType("text/html;charset=utf-8");
        int update = studentService.update(student);
        if (update>0){
            response.getWriter().write("<script>alert('学生信息更新成功');location.href='/student_list?pageIndex=1&pageSize=5';</script>");
        }else {
            response.getWriter().write("<script>alert('学生信息更新失败');location.href='/student_toUpdate/{"+student.getId()+"};</script>");
        }
    }


    /**
     * 导出文件
     */

    @RequestMapping("/student_exportTo")
    public void student_exportTo(String name, @RequestParam(defaultValue = "0") Integer class_id, HttpServletResponse response) throws Exception {

        Map<String ,Object> map = new HashMap<>();
        map.put("name",name);
        map.put("class_id",class_id);
        List<Student> studentsByPage = studentService.findExportStudents(map);
        System.out.println("学生数据条数为"+studentsByPage.size());
        for (Student student : studentsByPage) {
            System.out.println(student.toString());
        }

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("学生信息");

        String[] massage = {"序号","工号","姓名","性别","生日","学校"};
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i <massage.length; i++){
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(massage[i]);
        }
        for (int i = 0; i <studentsByPage.size();){
            Student student = studentsByPage.get(i);

            HSSFRow row1 = sheet.createRow(++i);

            HSSFCell cell = row1.createCell(0);
            cell.setCellValue(student.getId());
            HSSFCell noCell = row1.createCell(1);
            noCell.setCellValue(student.getNo());
            HSSFCell nameCell = row1.createCell(2);
            nameCell.setCellValue(student.getName());
            HSSFCell sexCell = row1.createCell(3);
            sexCell.setCellValue(student.getSex());
            HSSFCell birthdayCell = row1.createCell(4);
            birthdayCell.setCellValue(student.getBirthday());
            HSSFCell schoolCell = row1.createCell(5);
            schoolCell.setCellValue(student.getSchool());

        }
        ServletOutputStream outputStream = response.getOutputStream();
        String fileName = "学生信息统计表.xls";
        response.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "utf-8"));
        workbook.write(outputStream);

    }


    /**
     * 导入文件
     * @param file
     * @param response
     * @throws Exception
     */
    @RequestMapping("/student_import")
    public void student_import(MultipartFile file,HttpServletResponse response)throws Exception{
        response.setContentType("text/html;charset=utf-8");
        InputStream inputStream = file.getInputStream();
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheetAt = workbook.getSheetAt(0);

        List<Student> students = new ArrayList<>();
        for (int i = 1; i <= sheetAt.getLastRowNum(); i++){
            HSSFRow row = sheetAt.getRow(i);
            if (row==null){
                continue;
            }

            String cellStr="";
            Student student = new Student();
            for (int j = 0; j < row.getLastCellNum(); j++){
                HSSFCell cell = row.getCell(j);
                if (cell == null) {// 单元格为空设置cellStr为空串
                    cellStr = "";
                } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {// 对布尔值的处理
                    cellStr = String.valueOf(cell.getBooleanCellValue());
                } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {// 对数字值的处理
                    cellStr = cell.getNumericCellValue() + "";
                } else {// 其余按照字符串处理
                    cellStr = cell.getStringCellValue();
                }
                if (j==0){
                    student.setNo(cellStr);
                }else if(j==1){
                    student.setName(cellStr);
                }else if(j==2){
                    student.setClass_id(Integer.valueOf(cellStr));
                }else if(j==3){
                    student.setSex(cellStr);
                }else if(j==4){
                    student.setPhone(cellStr);
                }else if(j==5){
                    student.setQq(cellStr);
                }else if(j==6){
                    student.setEmail(cellStr);
                }else if(j==7){
                    student.setCardno(cellStr);
                }else if(j==8){
                    student.setSchool(cellStr);
                }else if(j==9){
                    student.setEducation(cellStr);
                }else if(j==10){
                    student.setBirthday(cellStr);
                }else if(j==11){
                    student.setCreatedate(cellStr);
                }

            }
            students.add(student);

        }
        int i = studentService.insertBatch(students);
        if (i==students.size()){
            response.getWriter().write("<script>alert('学生信息全部导入成功');location.href='/student_list?pageIndex=1&pageSize=5';</script>");
        }else {
            int c=students.size()-i;
            response.getWriter().write("<script>alert('学生信息"+i+"条导入成功，"+c+"条导入失败');location.href='/student_list?pageIndex=1&pageSize=5';</script>");
        }
    }

    /**
     * 查找班级
     * @return
     */
    @RequestMapping("/findClass")
    @ResponseBody
    public List<Classes> finClass(){

        return classesService.findClassList();
    }


    /**
     * 查看详情
     * @param id
     * @param model
     * @return
     */
    @RequestMapping("/studentdetails/{id}")
    public String studentdetails(@PathVariable("id")int id,Model model){

        Student studentdetails = studentService.findStudentById(id);

        model.addAttribute("studentdetails",studentdetails);
        return "studentdetails";
    }

    /**
     * 新添数据
     */

    @RequestMapping("/add_student")
    public void addStudent(Student student,HttpServletResponse response) throws Exception{
        response.setContentType("text/html;charset=utf-8");


        int i = studentService.insertStudent(student);
        if (i>0){
            response.getWriter().write("<script>alert('学生信息添加成功');location.href='/student_list?pageIndex=1&pageSize=5';</script>");
        }else {
            response.getWriter().write("<script>alert('学生信息添加失败');location.href='/add_student';</script>");
        }

    }


//    @RequestMapping("/findStudentInfo")
//    @ResponseBody
//    public List<StudentInfo> findStudentInfo(Model model){
//        List<String> name = new ArrayList<>();
//        List<StudentInfo> studentInfo = studentService.findStudentInfo();
//        for (StudentInfo info : studentInfo) {
//            name.add(info.getName());
//        }
//
//        return studentInfo;
//    }

    @RequestMapping("/findStudentInfo")
    public String findStudentInfo(Model model) throws  Exception{
        ObjectMapper mapper = new ObjectMapper();
        List<String> name = new ArrayList<>();
        List<StudentInfo> studentInfo = studentService.findStudentInfo();
        for (StudentInfo info : studentInfo) {
            name.add(info.getName());
        }


        String name1 = mapper.writeValueAsString(name);
        String date1 = mapper.writeValueAsString(studentInfo);
        model.addAttribute("name",name1);
        model.addAttribute("date",date1);
        return "pie";
    }
}
